package database

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"strconv"
	"strings"
	"time"

	"github.com/pkg/errors"
	_ "modernc.org/sqlite"

	"changeme/internal/models"
)

// SQLiteDriverImpl SQLite数据库驱动实现
type SQLiteDriverImpl struct {
	BaseDriver
}

// NewSQLiteDriverImpl 创建SQLite驱动实例
func NewSQLiteDriverImpl() *SQLiteDriverImpl {
	return &SQLiteDriverImpl{
		BaseDriver: BaseDriver{
			Dialect: &SQLiteDialect{},
		},
	}
}

// Connect 连接到SQLite数据库
func (s *SQLiteDriverImpl) Connect(config *models.DatabaseConfig) error {
	if s.Connected {
		return ErrConnectionExists
	}

	if config.FilePath == "" {
		return errors.New("SQLite file path is required")
	}

	// 确保目录存在
	dir := filepath.Dir(config.FilePath)
	if dir != "." && dir != "" {
		if err := os.MkdirAll(dir, 0755); err != nil {
			return errors.Wrap(err, "failed to create directory for SQLite database")
		}
	}

	dsn := buildSQLiteDSN(config)
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return errors.Wrap(err, "failed to open sqlite connection")
	}

	// 设置连接池参数
	db.SetMaxOpenConns(1) // SQLite最好只有一个连接以避免锁问题
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(5 * time.Minute)

	// 设置pragmas
	if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
		db.Close()
		return errors.Wrap(err, "failed to enable foreign keys")
	}

	// 测试连接
	if err := db.Ping(); err != nil {
		db.Close()
		return errors.Wrap(err, "failed to ping sqlite database")
	}

	s.DB = db
	s.Config = config
	s.Connected = true

	return nil
}

// GetVersion 获取SQLite版本
func (s *SQLiteDriverImpl) GetVersion() (string, error) {
	if !s.IsConnected() {
		return "", ErrNotConnected
	}

	var version string
	err := s.DB.QueryRow("SELECT sqlite_version()").Scan(&version)
	if err != nil {
		return "", errors.Wrap(err, "failed to get sqlite version")
	}

	return version, nil
}

// ListDatabases 列出所有数据库 - SQLite只有一个数据库，返回当前数据库
func (s *SQLiteDriverImpl) ListDatabases(ctx context.Context) ([]models.DatabaseInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	// 对于SQLite，数据库就是文件，因此我们返回当前连接的数据库文件
	var databases []models.DatabaseInfo

	filename := s.Config.FilePath
	if !filepath.IsAbs(filename) {
		// 尝试获取绝对路径
		abs, err := filepath.Abs(filename)
		if err == nil {
			filename = abs
		}
	}

	// 获取文件信息
	fileInfo, err := os.Stat(filename)
	var size int64 = 0
	if err == nil {
		size = fileInfo.Size()
	}

	db := models.DatabaseInfo{
		Name:      filepath.Base(filename),
		Charset:   "UTF-8",  // SQLite默认使用UTF-8
		Collation: "binary", // SQLite默认排序规则
		Size:      size,
		Comment:   "SQLite database file: " + filename,
	}

	databases = append(databases, db)
	return databases, nil
}

// ListTables 列出指定数据库的所有表
func (s *SQLiteDriverImpl) ListTables(ctx context.Context, database string) ([]models.TableInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	// SQLite不使用database参数，忽略它

	// 查询所有表
	query := `
		SELECT 
			name,
			'main' as schema_name,
			CASE 
				WHEN type = 'table' THEN 'TABLE'
				WHEN type = 'view' THEN 'VIEW'
				ELSE type
			END as type,
			'sqlite' as engine,
			NULL as collation,
			0 as row_count,
			0 as data_length,
			0 as index_length,
			'' as comment,
			'' as created_at
		FROM sqlite_master
		WHERE type IN ('table', 'view')
		AND name NOT LIKE 'sqlite_%'
		ORDER BY name
	`

	rows, err := s.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to list tables")
	}
	defer rows.Close()

	var tables []models.TableInfo
	for rows.Next() {
		var table models.TableInfo
		var collation, comment, createdAt sql.NullString
		if err := rows.Scan(
			&table.Name, &table.Schema, &table.Type, &table.Engine,
			&collation, &table.RowCount, &table.DataLength,
			&table.IndexLength, &comment, &createdAt,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan table info")
		}

		if collation.Valid {
			table.Collation = collation.String
		}

		if comment.Valid {
			table.Comment = comment.String
		}

		if createdAt.Valid {
			table.CreatedAt = createdAt.String
		}

		// 尝试获取表的行数
		var count int64
		countErr := s.DB.QueryRowContext(ctx, fmt.Sprintf("SELECT COUNT(*) FROM \"%s\"", table.Name)).Scan(&count)
		if countErr == nil {
			table.RowCount = count
		}

		tables = append(tables, table)
	}

	return tables, nil
}

// GetTableColumns 获取表的列信息
func (s *SQLiteDriverImpl) GetTableColumns(ctx context.Context, database, table string) ([]models.ColumnInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	// 获取表的列信息
	query := fmt.Sprintf("PRAGMA table_info(\"%s\")", table)
	rows, err := s.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table columns")
	}
	defer rows.Close()

	var columns []models.ColumnInfo
	for rows.Next() {
		var cid int
		var name, typeName, notNull, defaultValue, pk sql.NullString
		if err := rows.Scan(&cid, &name, &typeName, &notNull, &defaultValue, &pk); err != nil {
			return nil, errors.Wrap(err, "failed to scan column info")
		}

		column := models.ColumnInfo{
			Name:     name.String,
			Type:     strings.ToLower(typeName.String),
			FullType: typeName.String,
			Position: cid,
		}

		// 处理类型和长度
		if strings.Contains(typeName.String, "(") {
			parts := strings.Split(typeName.String, "(")
			column.Type = strings.ToLower(parts[0])
			if len(parts) > 1 {
				lengthPart := strings.TrimRight(parts[1], ")")
				if lengthVal, err := strconv.Atoi(lengthPart); err == nil {
					column.Length = lengthVal
				}
			}
		}

		// NOT NULL
		column.NotNull = notNull.String == "1"
		column.IsNullable = !column.NotNull

		// 主键
		column.PrimaryKey = pk.String == "1"
		column.IsPrimaryKey = column.PrimaryKey

		// 自增 - SQLite中的自增是通过INTEGER PRIMARY KEY实现的
		column.AutoIncrement = column.PrimaryKey && strings.ToUpper(column.Type) == "INTEGER"
		column.IsAutoIncrement = column.AutoIncrement

		// 默认值
		if defaultValue.Valid {
			column.DefaultValue = defaultValue.String
		}

		// 检查是否是外键
		fkQuery := fmt.Sprintf("PRAGMA foreign_key_list(\"%s\")", table)
		fkRows, fkErr := s.DB.QueryContext(ctx, fkQuery)
		if fkErr == nil {
			defer fkRows.Close()
			for fkRows.Next() {
				var id, seq int
				var fkTable, from, to, onUpdate, onDelete, match sql.NullString
				if err := fkRows.Scan(&id, &seq, &fkTable, &from, &to, &onUpdate, &onDelete, &match); err != nil {
					continue
				}
				if from.String == column.Name {
					column.Comment = fmt.Sprintf("Foreign key to %s.%s", fkTable.String, to.String)
					break
				}
			}
		}

		column.RawInfo = map[string]interface{}{
			"cid":          cid,
			"name":         name.String,
			"type":         typeName.String,
			"notNull":      notNull.String,
			"defaultValue": defaultValue.String,
			"pk":           pk.String,
		}

		columns = append(columns, column)
	}

	// 使用格式化工具处理列信息
	return FormatColumns(columns, models.SQLite), nil
}

// GetTableIndexes 获取表的索引信息
func (s *SQLiteDriverImpl) GetTableIndexes(ctx context.Context, database, table string) ([]models.IndexInfo, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	// 获取索引列表
	query := fmt.Sprintf("PRAGMA index_list(\"%s\")", table)
	rows, err := s.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table indexes")
	}
	defer rows.Close()

	var indexes []models.IndexInfo
	for rows.Next() {
		var seq int
		var name, unique sql.NullString
		var origin, partial sql.NullString // SQLite 3.9.0+ 有这些字段

		// 尝试扫描所有字段，如果失败就回退到基本字段
		err := rows.Scan(&seq, &name, &unique, &origin, &partial)
		if err != nil {
			// 回退到基本字段
			rows.Close()
			rows, err = s.DB.QueryContext(ctx, query)
			if err != nil {
				return nil, errors.Wrap(err, "failed to retry index list query")
			}
			for rows.Next() {
				if err := rows.Scan(&seq, &name, &unique); err != nil {
					return nil, errors.Wrap(err, "failed to scan index info")
				}
				break
			}
		}

		if !name.Valid {
			continue
		}

		index := models.IndexInfo{
			Name:     name.String,
			IsUnique: unique.String == "1",
			Type:     "BTREE", // SQLite默认使用B-tree索引
		}

		// 获取索引的列
		indexInfoQuery := fmt.Sprintf("PRAGMA index_info(\"%s\")", name.String)
		indexInfoRows, err := s.DB.QueryContext(ctx, indexInfoQuery)
		if err == nil {
			defer indexInfoRows.Close()
			var columns []string
			for indexInfoRows.Next() {
				var seqno, cid int
				var colName sql.NullString
				if err := indexInfoRows.Scan(&seqno, &cid, &colName); err != nil {
					continue
				}
				if colName.Valid {
					columns = append(columns, colName.String)
				}
			}
			index.Columns = columns
		}

		// 检查是否是主键索引
		if strings.HasPrefix(name.String, "sqlite_autoindex_") {
			// 自动创建的索引，可能是主键
			tableInfoQuery := fmt.Sprintf("PRAGMA table_info(\"%s\")", table)
			tableInfoRows, err := s.DB.QueryContext(ctx, tableInfoQuery)
			if err == nil {
				defer tableInfoRows.Close()

				// 收集主键列
				var pkColumns []string
				for tableInfoRows.Next() {
					var cid int
					var name, typeName, notNull, defaultValue, pk sql.NullString
					if err := tableInfoRows.Scan(&cid, &name, &typeName, &notNull, &defaultValue, &pk); err != nil {
						continue
					}
					if pk.String == "1" && name.Valid {
						pkColumns = append(pkColumns, name.String)
					}
				}

				// 检查索引列是否与主键列匹配
				if len(pkColumns) > 0 && len(pkColumns) == len(index.Columns) {
					isMatch := true
					for i, col := range pkColumns {
						if i >= len(index.Columns) || col != index.Columns[i] {
							isMatch = false
							break
						}
					}
					if isMatch {
						index.IsPrimary = true
					}
				}
			}
		}

		indexes = append(indexes, index)
	}

	return indexes, nil
}

// ExecuteQuery 执行查询语句
func (s *SQLiteDriverImpl) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	start := time.Now()
	rows, err := s.DB.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	// 获取列名
	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 获取列类型
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	// 读取数据
	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 尝试确定数据类型
					if colTypes != nil && i < len(colTypes) {
						// 检查数据库列类型
						dbType := strings.ToLower(colTypes[i].DatabaseTypeName())

						// 对于整数类型
						if strings.Contains(dbType, "int") {
							// 尝试转换为整数
							if intVal, err := strconv.ParseInt(string(b), 10, 64); err == nil {
								if intVal > 9007199254740991 || intVal < -9007199254740991 {
									// 超出JavaScript安全整数范围
									row[col] = string(b)
								} else {
									row[col] = intVal
								}
								continue
							}
						}

						// 对于浮点类型
						if strings.Contains(dbType, "real") || strings.Contains(dbType, "float") || strings.Contains(dbType, "double") {
							if floatVal, err := strconv.ParseFloat(string(b), 64); err == nil {
								row[col] = floatVal
								continue
							}
						}
					}

					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 处理int64类型，确保不超出JavaScript安全整数范围
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

// ExecuteNonQuery 执行非查询语句
func (s *SQLiteDriverImpl) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	if !s.IsConnected() {
		return 0, ErrNotConnected
	}

	result, err := s.DB.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows")
	}

	return affected, nil
}

// BeginTransaction 开始事务
func (s *SQLiteDriverImpl) BeginTransaction(ctx context.Context) (DatabaseTransaction, error) {
	if !s.IsConnected() {
		return nil, ErrNotConnected
	}

	tx, err := s.DB.BeginTx(ctx, nil)
	if err != nil {
		return nil, errors.Wrap(err, "failed to begin transaction")
	}

	return &SQLiteTransaction{tx: tx}, nil
}

// buildSQLiteDSN 构建SQLite连接字符串
func buildSQLiteDSN(config *models.DatabaseConfig) string {
	// 基本DSN是文件路径
	dsn := config.FilePath

	// 添加常用的SQLite参数
	params := []string{
		"_foreign_keys=1",     // 启用外键约束
		"_journal_mode=WAL",   // 使用WAL模式提高并发性
		"_synchronous=NORMAL", // 设置同步模式
		"_cache_size=2000",    // 增加缓存大小
	}

	// 添加用户自定义选项
	if config.Options != "" {
		params = append(params, config.Options)
	}

	// 构建带参数的DSN
	if len(params) > 0 {
		dsn += "?" + strings.Join(params, "&")
	}

	// 记录DSN（去除敏感信息）
	fmt.Printf("SQLite DSN: %s\n", dsn)

	return dsn
}

// SQLiteTransaction SQLite事务实现
type SQLiteTransaction struct {
	tx *sql.Tx
}

func (st *SQLiteTransaction) Commit() error {
	return st.tx.Commit()
}

func (st *SQLiteTransaction) Rollback() error {
	return st.tx.Rollback()
}

func (st *SQLiteTransaction) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	start := time.Now()
	rows, err := st.tx.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 获取列类型
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 尝试确定数据类型
					if colTypes != nil && i < len(colTypes) {
						// 检查数据库列类型
						dbType := strings.ToLower(colTypes[i].DatabaseTypeName())

						// 对于整数类型
						if strings.Contains(dbType, "int") {
							// 尝试转换为整数
							if intVal, err := strconv.ParseInt(string(b), 10, 64); err == nil {
								if intVal > 9007199254740991 || intVal < -9007199254740991 {
									// 超出JavaScript安全整数范围
									row[col] = string(b)
								} else {
									row[col] = intVal
								}
								continue
							}
						}

						// 对于浮点类型
						if strings.Contains(dbType, "real") || strings.Contains(dbType, "float") || strings.Contains(dbType, "double") {
							if floatVal, err := strconv.ParseFloat(string(b), 64); err == nil {
								row[col] = floatVal
								continue
							}
						}
					}

					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 处理int64类型，确保不超出JavaScript安全整数范围
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

func (st *SQLiteTransaction) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	result, err := st.tx.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query in transaction")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows in transaction")
	}

	return affected, nil
}

// Exec 执行SQL命令，返回sql.Result
func (st *SQLiteTransaction) Exec(query string, args ...interface{}) (sql.Result, error) {
	if st.tx == nil {
		return nil, errors.New("transaction is nil or already committed")
	}

	return st.tx.Exec(query, args...)
}
